<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_custodian Extends MY_Model
{
	private static $ttable = 'custodian_items_transactions';
	private static $itable = 'custodian_items';
	private static $ctable = 'custodian_items_categories';
	
	public function add_category($data)
	{
		$input['cic_name'] = $data['cat_name'];
		$input['cic_desc'] = $data['cat_desc'];
		$input['cic_created'] = NOW;
		
		$this->db->insert(self::$ctable,$input);
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	public function get_categories()
	{
		$q = $this->db->select('cic_id,cic_name,cic_desc')
					  ->get(self::$ctable);
		return $q->num_rows() >=1 ? $q->result() : FALSE;
	}
	
	public function get_categories_array()
	{
		$get = $this->db->select('cic_id,cic_name')
					 ->get(self::$ctable);
		
		if($get->num_rows() >= 1)
		{	$options[''] = '--Choose Category--';
			foreach($get->result() as $val)
			{
				$options[$val->cic_id] = $val->cic_name;
			}
			return $options;
		}else{
			return array(''=>'no category');
		}
	}
	
	public function get_items_array()
	{
		
	
	}
	
	public function add_item($data)
	{
		$input['ci_name'] = $data['item']['item_name'];
		$input['ci_desc'] = $data['item']['item_desc'];
		$input['ci_qty'] = $data['item']['item_qty'];
		$input['ci_cic_id'] = $data['item']['item_cat'];
		$input['ci_itm_brand'] = $data['item_brand'];
		$input['ci_itm_ser'] = $data['item_serial'];
		$input['ci_itm_msrmt'] = $data['measure'];
		$input['ci_itm_uprice'] = $data['item_unit_price'];
		$input['ci_itm_pprice'] = $data['item_purchase_price'];
		$input['ci_created'] = NOW;
		
		
		// pd($input);
		$this->db->insert(self::$itable,$input);
		return $this->db->affected_rows() >=1 ? TRUE : FALSE;
	}
	
	public function get_items()
	{
		$q = $this->db->select('ci_id,ci_name,ci_desc,ci_qty,cic_name')
				 ->join(self::$ctable,'cic_id = ci_cic_id','LEFT')
				 ->order_by('cic_name','ASC')
				 ->get(self::$itable);
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}
	
	public function add_transaction()
	{
	
	
	
	}
	
	public function fetch_transactions($start='',$limit='',$return = FALSE)
	{
		$start = is_numeric($start)? intval($start) : 0;
		$limit = is_numeric($limit) ? intval($limit) : 0;
		$r = $return == FALSE ? 0 : 1;
		$sql = 'SELECT cht.desc as description,
					 cht.id as transaction_id,
					 DATE_FORMAT(cht.dateissued,"%M %d , %Y") as date,
					 concat(p.firstname," ",p.middlename," ",p.lastname) as fullname
			  FROM custodian_header_transaction cht
			  LEFT JOIN enrollments e ON e.e_id = cht.student_id
			  LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
			  WHERE cht.returned = ?
			  LIMIT '.$limit.' , '.$start;
			  
		$q = $this->db->query($sql,array($r));
		return $q->num_rows() >=1 ? $q->result() : FALSE;
	}
	
	public function count_all_transactions()
	{
		
	
	}
	
	public function get_items_with_category()
	{
		$q = $this->db->select('cic_id,cic_name')->get('custodian_items_categories');
		
		if($q->num_rows() >= 1 )
		{
			foreach($q->result() as $k => $v)
			{
				$result[$v->cic_name] = $this->_get_items($v->cic_id);
			}
			return $result;
		}else{
			return FALSE;
		}
	}
	
	private function _get_items($id)
	{
		$q = $this->db->select('ci_id,ci_name')->where('ci_cic_id',$id)->get('custodian_items');
		
		if($q->num_rows() >= 1 )
		{
			foreach($q->result() as $k => $vv)
			{
				$result[$vv->ci_id] = $vv->ci_name;
			}
			return $result;
		
		}else{
			return NULL;
		}
	}
	
	
	public function get_items_where($term)
	{
		$sql = 'SELECT ci_id as fee_id,
					   ci_name as label
				FROM custodian_items ci
				LEFT JOIN custodian_items_categories cic ON cic.cic_id = ci.ci_cic_id
				WHERE ci.ci_name like ?
				OR cic.cic_name like ?';
				
		$q = $this->db->query($sql,array('%'.$term.'%','%'.$term.'%'));
		
		return $q->num_rows() >= 1 ? $q->result_array() : false;	
	}
	
	public function insert_new_transaction($data)
	{
		$data = (object)$data;

		$for_header['student_id'] = $this->get_enrollment_id_by_student_id($data->nameofstudent);
		$for_header['desc'] = $data->description;
		$for_header['dateissued'] = (new DateTime)->createFromFormat('m/d/Y',$data->dateissued)->format('Y-m-d');
		
		$this->db->insert('custodian_header_transaction',$for_header);
		$insert_id =  $this->db->insert_id();
		
		if($this->db->affected_rows() >= 1)
		{
			if($data->quantity)
			{
				foreach($data->quantity as $k => $v)
				{
					$for_items[$k]['header_id'] = $insert_id;
					$for_items[$k]['qty'] = $v;
					$for_items[$k]['items_id'] = $data->desc[$k];
					$for_items[$k]['item_name'] = $this->_get_item_desc($data->desc[$k]);
				}
			}
			
			$this->db->insert_batch('custodian_transaction_items',$for_items);
			if($this->db->affected_rows() >= 1)
			{
				return (object)array('status'=>TRUE,'log'=>'Successfully inserted transaction.');
			}else{
				return (object)array('status'=>FALSE,'log'=>'Unable to insert Transaction.');
			}
		}else{
			return (object)array('status'=>FALSE,'log'=>'Unable to insert Transaction.');
		}
	}
	
	private function _get_item_desc($id)
	{
		$q = $this->db->select('concat(ci_name," | ",ci_desc) as item',FALSE)
				 ->where('ci_id',$id)->limit(1)
				 ->get('custodian_items');
	
		return $q->num_rows() >= 1 ? $q->row()->item : NULL;
	}
	
	public function get_enrollment_id_by_student_id($student_id)
	{
		$q = $this->db->select('e_id')
				 ->where('e_student_id',$student_id)
				 ->where('e_archived',0)
				 ->limit(1)
				 ->get('enrollments');
				 
		return $q->num_rows() >= 1 ? $q->row()->e_id : FALSE;
	}

	public function check_quantity($id)
	{
	
	}
	
	public function view_transaction_details($t_id)
	{
		
		$sql = 'SELECT cht.desc as description,
					 cht.id as transaction_id,
					 DATE_FORMAT(cht.dateissued,"%M %d , %Y") as date,
					 concat(p.firstname," ",p.middlename," ",p.lastname) as fullname
			  FROM custodian_header_transaction cht
			  LEFT JOIN enrollments e ON e.e_id = cht.student_id
			  LEFT JOIN profiles p ON p.profile_id = e.e_profile_id
			  WHERE cht.id = ?
			  LIMIT 1';
			
		$q1 = $this->db->query($sql,array($t_id));
		
		
		if($q1->num_rows() >= 1 )
		{
			$q2 = $this->db->select('item_name as item,
									 qty as quant',FALSE)
				 ->where('header_id',$t_id)
				 ->get('custodian_transaction_items');

			if($q2->num_rows() >= 1)
			{
				$result['header'] = $q1->row();
				$result['items'] = $q2->result();
				
				return (object)$result;
				
				// pd($result);
			}else{
				return FALSE;
			}
		}else{
			return FALSE;
		}
	}
	
	public function delete_transaction($id)
	{
		$this->db->trans_start();
		$this->db->where('id',$id)->delete('custodian_header_transaction');
		$this->db->where('header_id',$id)->delete('custodian_transaction_items');
		$this->db->trans_complete();
		
		return $this->db->trans_status();
	}
}